/*
 * HW2 database systems
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "wet.h"
#include "libpq-fe.h"

#define MAX_QUERY 1000
#define DATE_FORMAT 10
#define NO_TAGS "0"
#define NO_ANSWERS "0"

#define CHECK_SELECT_RES_OK(res) if(!res || PQresultStatus(res) != PGRES_TUPLES_OK) { \
									fprintf(stdout, "Error executing query: %s\n", \
									PQresultErrorMessage(res));\
									PQclear(res);\
									return NULL;\
						  	  	 }

#define CHECK_CMD_RES_OK(res) if(!res || PQresultStatus(res) != PGRES_COMMAND_OK) { \
									fprintf(stderr, "Error executing query: %s\n", \
									PQresultErrorMessage(res));\
									PQclear(res);\
									return NULL;\
						  	  	 }

PGconn* conn;
int relatedTagsNum;
int relatedQuestionsNum;
//TODO: delete this function in the end of debug
void RESET() {
	PQclear(PQexec(conn,"DROP TABLE tags CASCADE;"));
	PQclear(PQexec(conn,"DROP TABLE answers CASCADE;"));
	PQclear(PQexec(conn,"DROP TABLE questions CASCADE;"));
	PQclear(PQexec(conn,"CREATE TABLE tags AS SELECT * FROM course_tags;"));
	PQclear(PQexec(conn,"CREATE TABLE answers AS SELECT * FROM course_answers;"));
	PQclear(PQexec(conn,"CREATE TABLE questions AS SELECT * FROM course_questions;"));
}

void* addQuestion(const char* question, const char* username, const char* date){
	char command[MAX_QUERY];
	PGresult* res;

	//check if user already asked a question today
	sprintf(command,"SELECT * FROM questions WHERE asker='%s' AND date='%s'",username,date);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) != 0){
		printf(EXISTING_RECORD);
		PQclear(res);
		return 0;
	}
	PQclear(res);


	//find biggest id for the questions, and assign id+1 to new one
	sprintf(command,"SELECT MAX(qid) FROM questions");
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	int id=atoi(PQgetvalue(res,0,0))+1;//assign new id
	PQclear(res);


	//add question with new id
	sprintf(command,"INSERT INTO questions(qid,question,asker,date) VALUES(%d,'%s','%s','%s')",id,question,username,date);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",id);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	printf(QUESTIONS_TABLE_HEADER);
	printf(QUESTIONS_TABLE_LINE,PQgetvalue(res,0,0),PQgetvalue(res,0,1),PQgetvalue(res,0,2),PQgetvalue(res,0,3),NO_TAGS);
	PQclear(res);


	return 0;
}

void* deleteQuestion(const int qid){
	char command[MAX_QUERY];
	PGresult* res;

	//check if question exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//delete the question
	sprintf(command,"DELETE FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	//delete corresponding answers
	sprintf(command,"DELETE FROM answers WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	//delete corresponding tags
	sprintf(command,"DELETE FROM tags WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	return 0;
}

void* editQuestion(const int qid, const char* question){
	char command[MAX_QUERY];
	PGresult* res;

	//check if question exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//update record with new question
	sprintf(command,"UPDATE questions SET question='%s' WHERE qid=%d",question,qid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	//check if no tags for this question
	sprintf(command,"SELECT tags.tag FROM tags WHERE tags.qid = %d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		PQclear(res);
		sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		printf(QUESTIONS_TABLE_HEADER);//question id|question|user|date| #tags |
		printf(QUESTIONS_TABLE_LINE,PQgetvalue(res,0,0),PQgetvalue(res,0,1),PQgetvalue(res,0,2),PQgetvalue(res,0,3),NO_TAGS);
		PQclear(res);

		return 0;
	}
	PQclear(res);
	//create a print view with the number of corresponding tags
	sprintf(command,"SELECT questions.qid, questions.question, questions.asker, questions.date, count(tags.qid) AS numoftags "
			"FROM questions, tags "
			"WHERE tags.qid = %d AND questions.qid = %d "
			"GROUP BY questions.qid, questions.question, questions.asker, questions.date",qid,qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	printf(QUESTIONS_TABLE_HEADER);
	printf(QUESTIONS_TABLE_LINE,PQgetvalue(res,0,0),PQgetvalue(res,0,1),PQgetvalue(res,0,2),PQgetvalue(res,0,3),PQgetvalue(res,0,4));
	PQclear(res);
	return 0;
}

void* addAnswer(const int qid, const char* answer){
	char command[MAX_QUERY];
	PGresult* res;

	//check if qid exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//find biggest id for the answer to the qid, and assign id+1 to new one
	sprintf(command,"SELECT MAX(aid) FROM answers");
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	int aid=atoi(PQgetvalue(res,0,0))+1;//assign new id
	PQclear(res);

	//add question with new id
	sprintf(command,"INSERT INTO answers VALUES(%d,%d,'%s',%d)",aid,qid,answer,0);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	printf(ANSWERS_TABLE_HEADER);//aid,qid,answer,#approves
	char cqid[MAX_QUERY],caid[MAX_QUERY];
	sprintf(cqid,"%d",qid);
	sprintf(caid,"%d",aid);
	printf(ANSWERS_TABLE_LINE,caid,cqid,answer,NO_ANSWERS);//no one approved it yet
	PQclear(res);

	return 0;
}

void* editAnswer(const int aid, const char* answer){
	char command[MAX_QUERY];
	PGresult* res;

	//check to see if answer exists
	sprintf(command,"SELECT * FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//update record with new answer
	sprintf(command,"UPDATE answers SET answer='%s' WHERE aid=%d",answer,aid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	//print new answer
	sprintf(command,"SELECT qid,approves FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	printf(ANSWERS_TABLE_HEADER);
	char caid[MAX_QUERY];
	sprintf(caid,"%d",aid);
	printf(ANSWERS_TABLE_LINE,caid,PQgetvalue(res,0,0),answer,PQgetvalue(res,0,1));
	PQclear(res);

	return 0;
}

void* deleteAnswer(const int aid){
	char command[MAX_QUERY];
	PGresult* res;

	//check if an answer with aid exists
	sprintf(command,"SELECT * FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//delete the record
	sprintf(command,"DELETE FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	return 0;
}

void* addTag(const int qid, const char* tag){
	char command[MAX_QUERY];
	PGresult* res;

	//check if qid exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//check if tag already exists for this question
	sprintf(command,"SELECT tag FROM tags WHERE qid=%d AND tag='%s'",qid,tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) != 0){
		printf(EXISTING_RECORD);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//insert new tag for this question
	sprintf(command,"INSERT INTO tags VALUES(%d,'%s')",qid,tag);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	printTags(qid);

	return 0;
}

void* removeTag(const int qid, const char* tag){
	char command[MAX_QUERY];
	PGresult* res;

	//check if qid exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//check if tag exists
	sprintf(command,"SELECT tag FROM tags WHERE qid=%d AND tag='%s'",qid,tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	/*int i=0;
	char* strtags=PQgetvalue(res,0,0);
	int valid = 0;
	while (strtags!=NULL){
		if (strcmp(strtags,tag)==0){
			valid=1;
		}
		i++;
		strtags=PQgetvalue(res,i,0);
	}
	if (valid == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}*/
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}

	PQclear(res);

	//delete corresponding tag
	sprintf(command,"DELETE FROM tags WHERE qid=%d AND tag='%s'",qid,tag);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	printTags(qid);

	return 0;
}
void* printTags(const int qid){
	char command[MAX_QUERY];
	PGresult* res;

	//check that qid exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//check that the qid question has tags in tags table
	sprintf(command,"SELECT * FROM tags WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(EMPTY);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//print tags for requested qid
	sprintf(command,"SELECT tags.tag FROM tags WHERE tags.qid = %d ORDER BY tags.tag",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	printf(TAGS_TABLE_HEADER);
	int i;
	for (i=0;i < PQntuples(res);i++){
		printf(TAGS_TABLE_LINE,PQgetvalue(res,i,0));
	}
	PQclear(res);

	return 0;
}

void* approveAnswer(const int aid){
	char command[MAX_QUERY];
	PGresult* res;

	//check that answer with aid exists
	sprintf(command,"SELECT * FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//find the approves value for aid
	sprintf(command,"SELECT approves FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	//increase by 1
	int approves=atoi(PQgetvalue(res,0,0))+1;
	PQclear(res);

	//update new approves value for aid
	sprintf(command,"UPDATE answers SET approves=%d WHERE aid=%d",approves,aid);
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	//print the answer with aid and new approves value
	sprintf(command,"SELECT * FROM answers WHERE aid=%d",aid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	printf(ANSWERS_TABLE_HEADER);
	printf(ANSWERS_TABLE_LINE,PQgetvalue(res,0,0),PQgetvalue(res,0,1),PQgetvalue(res,0,2),PQgetvalue(res,0,3));
	PQclear(res);

	return 0;
}

void* findAnswers(const int qid){
	char command[MAX_QUERY];
	PGresult* res;

	//check that qid exists
	sprintf(command,"SELECT * FROM questions WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//find answers for this qid
	sprintf(command,"SELECT * FROM answers WHERE qid=%d",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	//check that the qid has answers
	if (PQntuples(res) == 0){
		printf(EMPTY);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//print answers
	printf(ANSWERS_FOR_QUESTION_TABLE_HEADER);
	sprintf(command,"SELECT answers.aid, answers.answer, answers.approves FROM answers WHERE qid=%d ORDER BY approves DESC, aid ASC",qid);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	int i;
	for (i=0; i < PQntuples(res);i++){
		printf(ANSWERS_FOR_QUESTION_TABLE_LINE,
			   PQgetvalue(res,i,0),
			   PQgetvalue(res,i,1),
			   PQgetvalue(res,i,2));
	}
	PQclear(res);

	return 0;
}

void* searchByTag(const char* tag){
	char command[MAX_QUERY];
	PGresult* res;

	//check that the tag exists
	sprintf(command,"SELECT tag FROM tags WHERE tag='%s'",tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);

	//find all related questions for this tag
	sprintf(command,"SELECT questions.qid, questions.question, questions.asker, questions.date, count(temp.qid) AS numoftags "
					"FROM questions, tags, tags AS temp "
					"WHERE tags.tag = '%s' AND questions.qid = tags.qid AND questions.qid = temp.qid "
					"GROUP BY questions.qid, questions.question, questions.asker, questions.date "
					"ORDER BY questions.qid",tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	int i=0;
	printf(QUESTIONS_TABLE_HEADER);
	for (i=0; i < PQntuples(res);i++){
		printf(QUESTIONS_TABLE_LINE,
			   PQgetvalue(res,i,0),
			   PQgetvalue(res,i,1),
			   PQgetvalue(res,i,2),
			   PQgetvalue(res,i,3),
			   PQgetvalue(res,i,4));
	}
	PQclear(res);

	return 0;
}

void* findRelatedTags(const char* tag){
	char command[MAX_QUERY];
	PGresult* res;

	//check that the tag exists
	sprintf(command,"SELECT tag FROM tags WHERE tag='%s'",tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(ILL_PARAMS);
		PQclear(res);
		return 0;
	}
	PQclear(res);
	//find the related tags not including the tags in question.
	sprintf(command,"SELECT temp.tag "
					"FROM tags, tags temp "
					"WHERE tags.tag = '%s' AND tags.qid = temp.qid "
					"EXCEPT "
					"SELECT tags.tag "
					"FROM tags "
					"WHERE tags.tag = '%s'",tag,tag);
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(EMPTY);
		PQclear(res);
		return 0;
	}
	printf(TAGS_TABLE_HEADER);
	int i;
	for (i=0;i < PQntuples(res);i++){
		printf(TAGS_TABLE_LINE,PQgetvalue(res,i,0));
	}
	PQclear(res);

	return 0;
}


void* findRelatedQuestions(const int qid){
	char command[MAX_QUERY];
	PGresult* res;
	int i;

        //check that the question exists
        sprintf(command,"SELECT qid FROM questions WHERE qid=%d",qid);
        res=PQexec(conn,command);
        CHECK_SELECT_RES_OK(res)
        if (PQntuples(res) == 0){
            printf(ILL_PARAMS);
            PQclear(res);
            return 0;
    }
    PQclear(res);

    sprintf(command,"SELECT questions.*, cnt FROM questions, (SELECT related_tags.qid, COUNT(tags.tag) AS cnt FROM (SELECT tags.qid, tags.tag FROM tags, (SELECT tag FROM tags WHERE qid=%d) AS question_tags WHERE tags.qid<>%d AND tags.tag = question_tags.tag) AS related_tags LEFT OUTER JOIN tags ON(tags.qid = related_tags.qid AND tags.tag <> related_tags.tag) GROUP BY related_tags.qid) AS question_tags_count WHERE questions.qid = question_tags_count.qid ORDER BY questions.qid", qid, qid);
    res=PQexec(conn,command);
    CHECK_SELECT_RES_OK(res)
    if (PQntuples(res) == 0){
        printf(EMPTY);
        PQclear(res);
        return 0;
    }
	
    relatedQuestionsNum=PQntuples(res);
	printf(QUESTIONS_TABLE_HEADER);
	for (i = 0; i < PQntuples(res); i++){
		printf(QUESTIONS_TABLE_LINE,PQgetvalue(res,i,0),PQgetvalue(res,i,1),PQgetvalue(res,i,2),PQgetvalue(res,i,3),PQgetvalue(res,i,4));
	}
	PQclear(res);

    return 0;
}

void* printStatistics(){
	char command[MAX_QUERY];
	PGresult* res;
	char tempTags[MAX_QUERY][MAX_QUERY];
	int numOfRelatedTags[MAX_QUERY];
	int numOfRelatedQuetions[MAX_QUERY];
	int numOfRelatedQuestionsWithAnswer[MAX_QUERY];
	int numOfRelatedQuestionsWithoutAnswer[MAX_QUERY];
	//check that there are tags.
	sprintf(command,"SELECT DISTINCT tag FROM tags ORDER BY tag");
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(EMPTY);
		PQclear(res);
		return 0;
	}
	int i, numOfTags=PQntuples(res);
	//save all tags
	for (i=0;i < numOfTags; i++){
		strcpy(tempTags[i],PQgetvalue(res,i,0));
	}
	PQclear(res);
	for (i=0;i < numOfTags; i++){
		//find all questions with this tag
		sprintf(command,"SELECT qid FROM tags WHERE tag='%s'",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		numOfRelatedQuetions[i]=PQntuples(res);
		PQclear(res);
		//find all questions with this tag that have answers
		sprintf(command,"SELECT tags.qid FROM tags WHERE tags.tag = '%s' "
						"INTERSECT SELECT answers.qid FROM answers",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		numOfRelatedQuestionsWithAnswer[i]=PQntuples(res);
		PQclear(res);
		//find all questions with this tag that have answers
		sprintf(command,"SELECT tags.qid FROM tags WHERE tags.tag = '%s' "
								"EXCEPT SELECT answers.qid FROM answers",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		numOfRelatedQuestionsWithoutAnswer[i]=PQntuples(res);
		PQclear(res);
		//find all related tags for this tag
		sprintf(command,"SELECT temp.tag "
						"FROM tags, tags temp "
						"WHERE tags.tag = '%s' AND tags.qid = temp.qid "
						"EXCEPT "
						"SELECT tags.tag "
						"FROM tags "
						"WHERE tags.tag = '%s'",tempTags[i],tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		numOfRelatedTags[i]=PQntuples(res);
		PQclear(res);
	}
	char cNORQ[MAX_QUERY];
	char cNORQWA[MAX_QUERY];
	char cNORQWOA[MAX_QUERY];
	char cNORTags[MAX_QUERY];

	printf(STATISTICS_TABLE_HEADER);
	for (i=0;i < numOfTags; i++){//tag| #question | #answered | #unanswered | #related
		sprintf(cNORQ,"%d",numOfRelatedQuetions[i]);
		sprintf(cNORQWA,"%d",numOfRelatedQuestionsWithAnswer[i]);
		sprintf(cNORQWOA,"%d",numOfRelatedQuestionsWithoutAnswer[i]);
		sprintf(cNORTags,"%d",numOfRelatedTags[i]);
		printf(STATISTICS_TABLE_LINE,
				tempTags[i],
				cNORQ,
				cNORQWA,
				cNORQWOA,
				cNORTags);
	}

	return 0;
}
void* interestingTags(){
	//printf("interestingTags:\n");
	char command[MAX_QUERY];
	PGresult* res;
	char tempTags[MAX_QUERY][MAX_QUERY]={"0"};
	//check that there are tags.
	sprintf(command,"SELECT tag FROM tags ORDER BY tag");
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	if (PQntuples(res) == 0){
		printf(EMPTY);
		PQclear(res);
		return 0;
	}
	int i,j, numOfTags=PQntuples(res);
	//save all tags in lexicographic order
	for (i=0; i < numOfTags; i++){
		strcpy(tempTags[i],PQgetvalue(res,i,0));
	}
	PQclear(res);

	//start measuring interest rate for each tag
	int interestRate[MAX_QUERY]={0};
	for (i=0; i < numOfTags; i++){
		//add number of questions related to the tag
		sprintf(command,"SELECT qid FROM tags WHERE tag='%s'",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		interestRate[i]+=PQntuples(res);
		PQclear(res);
		//add number of answers to the related questions
		sprintf(command,"SELECT answers.aid "
						"FROM ( SELECT tags.qid "
								"FROM tags "
								"WHERE tags.tag = '%s') AS relevant_qs, "
								"answers "
						"WHERE relevant_qs.qid = answers.qid",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		interestRate[i]+=PQntuples(res);
		PQclear(res);
		//add the sum of number of approves for all related answers
		sprintf(command,"SELECT answers.approves "
						"FROM ( SELECT tags.qid "
								"FROM tags "
								"WHERE tags.tag = '%s') AS relevant_qs, "
								"answers "
						"WHERE relevant_qs.qid = answers.qid",tempTags[i]);
		res=PQexec(conn,command);
		CHECK_SELECT_RES_OK(res)
		for (j=0; j < PQntuples(res);j++){
			interestRate[i]+=atoi(PQgetvalue(res,j,0));
		}
		PQclear(res);
	}

	printf(INTERESTING_TABLE_HEADER);
	int maxIR=interestRate[0];
	char maxIRtags[MAX_QUERY][MAX_QUERY]={"0"};
	for (i=1; i < numOfTags; i++){
		if (interestRate[i]>maxIR){
			maxIR=interestRate[i];
		}
	}
	//find the tags with the most interest rate
	int maxIRcounter=0;
	int k, alreadyExists=0;
	j=0;

	for (i=0; i < numOfTags; i++){
		if (interestRate[i]==maxIR){
			for (k=0;k<j;k++){
				if (strcmp(maxIRtags[k],tempTags[i])==0){
					alreadyExists=1;
				}
			}
			if (alreadyExists) continue;
			strcpy(maxIRtags[j],tempTags[i]);
			//printf("added %s to maxIRtags at pos. %d\n",maxIRtags[j],j);
			maxIRcounter++;
			j++;
		}
	}

	//view only the tags with the max interest rate
	//printf("most interesting tags are:\n");
	sprintf(command,"CREATE VIEW maxIRtags AS "
					"SELECT tags.tag, questions.qid, questions.question FROM tags,questions WHERE tags.qid=questions.qid AND tag IN ('%s'",maxIRtags[0]);
	for (i=1; i < maxIRcounter; i++){
		strcat(command,",'");
		strcat(command,maxIRtags[i]);
		strcat(command,"'");
		//printf("%s\n",maxIRtags[i]);
	}
	strcat(command,") ORDER BY tag,qid");
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);

	sprintf(command,"SELECT maxIRtags.*,temp.numofanswers,temp.numofapproves FROM maxIRtags "
					"INNER JOIN (SELECT DISTINCT answers.qid, COUNT(answers.aid) AS numofanswers, SUM(answers.approves) AS numofapproves FROM answers GROUP BY answers.qid) AS temp "
					"ON maxIRtags.qid=temp.qid ORDER BY tag,qid");
	res=PQexec(conn,command);
	CHECK_SELECT_RES_OK(res)
	/*if (maxIRcounter!=PQntuples(res)){
		printf("maxIRcounter=%d <> %d=PQntuples\n",maxIRcounter,PQntuples(res));
	}*/

	for (i=0;i < PQntuples(res); i++){//tag | qid |question|#answers | #approves
		printf(INTERESTING_TABLE_LINE,PQgetvalue(res,i,0),PQgetvalue(res,i,1),PQgetvalue(res,i,2),PQgetvalue(res,i,3),PQgetvalue(res,i,4));
	}

	PQclear(res);

	sprintf(command,"DROP VIEW maxIRtags");
	res=PQexec(conn,command);
	CHECK_CMD_RES_OK(res)
	PQclear(res);



	return 0;
}

void* bestAnswersForTag(const char* tag){
	char command[MAX_QUERY];
    PGresult* res;
    int questionsCount, i;

    //check that the tag exists
    sprintf(command,"SELECT tag FROM tags WHERE tag='%s'",tag);
    res=PQexec(conn,command);
    CHECK_SELECT_RES_OK(res)
    if (PQntuples(res) == 0){
    	printf(ILL_PARAMS);
    	PQclear(res);
    	return 0;
    }
    PQclear(res);

    sprintf(command,"SELECT questions.qid, questions.question, answers.aid, answers.answer "
    				"FROM questions, answers, "
    					"(SELECT answers.qid, MAX(related_answers.approves) "
    					"AS max_approves "
    					"FROM answers, "
    						//find all the related_answers for those related_qids
    						"(SELECT related_qids.qid, answers.aid, answers.approves "
    						"FROM answers, "
    							//find all related_qids for the tag
    							"(SELECT qid FROM tags WHERE tag='%s') AS related_qids "
    						"WHERE related_qids.qid=answers.qid) "
    						"AS related_answers "
    					"WHERE answers.qid=related_answers.qid GROUP BY answers.qid) "
    					"AS best_questions_approves "
    				"WHERE questions.qid=answers.qid AND "
    					  "questions.qid=best_questions_approves.qid AND "
    					  "answers.approves=best_questions_approves.max_approves "
    				"ORDER BY questions.qid, answers.aid", tag);
    	res=PQexec(conn,command);
    	CHECK_SELECT_RES_OK(res)
    	if (PQntuples(res) == 0){
        	printf(EMPTY);
        	PQclear(res);
        	return 0;
    	}
	// no answers for questions that related to tag
        printf(BEST_TABLE_HEADER);
        for (i = 0; i < PQntuples(res); i++)
                printf(BEST_TABLE_LINE,PQgetvalue(res,i,0),PQgetvalue(res,i,1),PQgetvalue(res,i,2),PQgetvalue(res,i,3));

    	PQclear(res);

    	return 0;
}
void* deleteRedundantAnswers(){
	char command[MAX_QUERY];
        PGresult* res;
        
	//delete redundant answers
        sprintf(command,"DELETE FROM answers WHERE NOT EXISTS (SELECT * FROM (SELECT qid, MAX(approves) AS max_approves FROM answers GROUP BY qid) AS best_answers WHERE answers.qid=best_answers.qid AND answers.approves=best_answers.max_approves)");
        res=PQexec(conn,command);
        CHECK_CMD_RES_OK(res);
	PQclear(res);
	return 0;
}

int main(void)
{
        char connect_param[80];
        sprintf(connect_param, "host=pgsql.cs.technion.ac.il dbname=%s user=%s password=%s", USERNAME, USERNAME, PASSWORD);
        conn = PQconnectdb(connect_param);
        if (!conn || PQstatus(conn) == CONNECTION_BAD)
        {
                fprintf(stdout, "Connection to server failed %s\n", PQerrorMessage(conn));
                PQfinish(conn);
                return 1;
        }
        parseInput();
        PQfinish(conn);
        return 0;
}











/*#define MAX_LINE_SIZE 128

static char command_name[MAX_LINE_SIZE + 2],command_line[MAX_LINE_SIZE + 2];
static char* arguments;
static int qid, aid;
static int missing_argc, argc;
static char question[MAX_LINE_SIZE + 2], answer[MAX_LINE_SIZE + 2], username[MAX_LINE_SIZE + 2], tag[MAX_LINE_SIZE + 2], date[MAX_LINE_SIZE + 2];

static void goto_next_line(FILE* file) {
        while (!feof(file) && fgetc(file) != '\n');
}

static int check_command(char* expected_name) {
        return (!strcmp(command_name,expected_name));
}

static int check_arguments(int expected_argc) {
        return ((missing_argc = expected_argc - argc) <= 0);
}

static void illegalCommand() {
        printf("Illegal command!\n");
}*/
